import pandas as pd
import mysql.connector
from mysql.connector import Error

def import_excel_data():
    try:
        print("开始读取Excel文件...")
        df = pd.read_excel('D:/Cursor/Project1/goods.xlsx', engine='openpyxl')
        print(f"Excel文件读取成功,共有 {len(df)} 行数据")
        
        print("正在连接数据库...")
        connection = mysql.connector.connect(
            host='127.0.0.1',        # 数据库主机地址
            database='db2025',      # 数据库名称
            user='root',            # 数据库用户名
            password='ning1991' # 数据库密码
        )
        
        if connection.is_connected():
            cursor = connection.cursor()
            print("数据库连接成功")
            
            columns = list(df.columns)
            placeholders = ','.join(['%s'] * len(columns))
            print(columns,placeholders)
            
            insert_query = f"INSERT INTO goods ({','.join(columns)}) VALUES ({placeholders})"
            print("开始导入数据...")
            
            success_count = 0
            for index, row in df.iterrows():
                try:
                    # 处理空值，将 NaN 转换为 None
                    values = [None if pd.isna(x) else x for x in row]
                    # 只有当id不为None时才进行转换
                    if values[0] is not None:
                        values[0] = int(float(values[0]))
                    
                    cursor.execute(insert_query, tuple(values))
                    success_count += 1
                except Error as e:
                    print(f"第 {index+1} 行数据导入失败: {e}")
                    print(f"问题数据: {values}")
                except Exception as e:
                    print(f"第 {index+1} 行数据处理错误: {e}")
                    print(f"原始数据: {row}")
                
            connection.commit()
            print(f"数据导入完成，成功导入 {success_count} 行数据")
            
    except Error as e:
        print(f"数据库错误: {e}")
    except Exception as e:
        print(f"程序错误: {e}")
        
    finally:
        if 'connection' in locals() and connection.is_connected():
            cursor.close()
            connection.close()
            print("数据库连接已关闭")

if __name__ == "__main__":
    import_excel_data() 